<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <title></title>
    <script src="js/jquery-1.12.0.min.js"></script>
</head>
<body>
<div id="mytable"></div>
</body>
<script>
    var db = openDatabase('HF151103', '1.0', 'my db', 2 * 1024);
    db.transaction(function (tx) {
        tx.executeSql('CREATE TABLE IF NOT EXISTS equipment (' +
                'id         INTEGER PRIMARY KEY AUTOINCREMENT,' +
                'name       TEXT    UNIQUE,' +
                'type       INTEGER NOT NULL,' +
                'value_type INTEGER,' +
                'value      INTEGER,' +
                'degree     INTEGER DEFAULT ( 1 ),' +
                'price      INTEGER NOT NULL,' +
                'img   TEXT  NOT NULL )'
        );
    });
    db.transaction(function(tx){
        tx.executeSql('CREATE TABLE if not exists users ('+
                'id          INTEGER PRIMARY KEY AUTOINCREMENT,'+
                'login       TEXT    UNIQUE  NOT NULL,'+
                'password    TEXT    NOT NULL,'+
                'u_name      TEXT    UNIQUE  NOT NULL,'+
                'level       INTEGER NOT NULL,'+
                'money       INTEGER NOT NULL,'+
                'online_time FLOAT,'+
                'last_time   DATE)'
        );
        tx.executeSql("INSERT INTO [users] ([id], [login], [password], [u_name], [level], [money], [online_time], [last_time]) VALUES (1, 'admin', 123, 'qfwf', 1, 10, null, datetime('now'))");
        tx.executeSql("INSERT INTO [users] ([id], [login], [password], [u_name], [level], [money], [online_time], [last_time]) VALUES (3, 'HF151101', 123, 'zz', 1, 0, null, null)");
        tx.executeSql("INSERT INTO [users] ([id], [login], [password], [u_name], [level], [money], [online_time], [last_time]) VALUES (4, 'HF151102', 123, 'yy', 1, 0, null, null)");
    });
    db.transaction(function(tx){
        tx.executeSql('CREATE TABLE if not exists bikes ('+
                'id       INTEGER PRIMARY KEY AUTOINCREMENT,'+
                'name     TEXT,'+
                'addspeed INTEGER,'+
                'maxspeed INTEGER,'+
                'degree   INTEGER,'+
                'price    INTEGER NOT NULL,'+
                'img      TEXT)'
        );
    });
    db.transaction(function(tx){
        tx.executeSql('CREATE TABLE if not exists riders ('+
                'id      INTEGER PRIMARY KEY AUTOINCREMENT,'+
                'r_name  TEXT    UNIQUE  NOT NULL,'+
                'img     TEXT    NOT NULL,'+
                'skill   TEXT    NOT NULL,'+
                'price   INTEGER NOT NULL,'+
                'r_level INTEGER)'
        );
    });
    db.transaction(function(tx){
        tx.executeSql('CREATE TABLE if not exists mission ('+
                'id       INTEGER PRIMARY KEY AUTOINCREMENT,'+
                'name     TEXT,'+
                'descript TEXT,'+
                'level    INTEGER,'+
                'belong   INTEGER,'+
                'map      TEXT,'+
                'conf     TEXT,'+
                'img      TEXT )'
        );
        tx.executeSql("INSERT INTO mission (id,name,descript,level,belong,map,conf,img) VALUES (1,'陆地','yellow',1,1,null,null,null)");
        tx.executeSql("INSERT INTO mission (id,name,descript,level,belong,map,conf,img) VALUES (2,'天空','bule',2,1,null,null,null)");
        tx.executeSql("INSERT INTO mission (id,name,descript,level,belong,map,conf,img) VALUES (3,'大海','beauty',2,1,null,null,null)");
    });
    db.transaction(function(tx) {
        tx.executeSql('CREATE TABLE IF NOT EXISTS user_equip (' +
                'userid INTEGER REFERENCES users ( id ),' +
                'equipid INTEGER REFERENCES equipment ( id ),' +
                'blong_time DATE,' +
                'now_value INTEGER,' +
                'degree INTEGER,' +
                'CONSTRAINT pk_ue PRIMARY KEY ( userid, equipid ))'
        );
        tx.executeSql("INSERT INTO [user_equip] ([userid], [equipid], [blong_time], [now_value], [degree]) VALUES (3, 1, '2016-03-09', null, null)");
        tx.executeSql("INSERT INTO [user_equip] ([userid], [equipid], [blong_time], [now_value], [degree]) VALUES (3, 2, '2016-03-09', null, null)");
        tx.executeSql("INSERT INTO [user_equip] ([userid], [equipid], [blong_time], [now_value], [degree]) VALUES (4, 2, '2016-03-09', null, null)");
        tx.executeSql("INSERT INTO [user_equip] ([userid], [equipid], [blong_time], [now_value], [degree]) VALUES (4, 1, '2016-03-09', null, null)");
    });
    db.transaction(function(tx){
        tx.executeSql('CREATE TABLE user_rider ('+
                'userid     INTEGER REFERENCES users ( id ),'+
                'riderid    INTEGER REFERENCES rider ( id ),'+
                'blong_time DATE,'+
                'now_value  INTEGER,'+
                'degree     INTEGER,'+
                'CONSTRAINT pk_ur PRIMARY KEY ( userid, riderid ))'
        );
    });
    db.transaction(function(tx){
        tx.executeSql('CREATE TABLE user_bike ('+
                'userid     INTEGER REFERENCES users ( id ),'+
                'bikeid     INTEGER REFERENCES bike ( id ),'+
                'blong_time DATE,'+
                'now_value  INTEGER,'+
                'degree     INTEGER,'+
                'CONSTRAINT pk_ub PRIMARY KEY ( userid, bikeid ))'
        );
    });
    db.transaction(function(tx){
        tx.executeSql('CREATE TABLE match_result ('+
                'userid     INTEGER REFERENCES users ( id ),'+
                'missionid  INTEGER REFERENCES mission ( id ),'+
                'match_time DATE,'+
                'result     INTEGER,'+      //过关与否，0表示没过关；1表示已过关
                'award      INTEGER,'+
                'CONSTRAINT pk_mr PRIMARY KEY ( userid, missionid, match_time ))'//三列合在一起作为复合主键，这三个值在表中不能一起重复；
        );
        tx.executeSql("INSERT INTO match_result (userid,missionid,match_time,result,award) VALUES (1,1,datetime('now'),1,null)");
        tx.executeSql("INSERT INTO match_result (userid,missionid,match_time,result,award) VALUES (1,2,datetime('now'),0,null)");
        tx.executeSql("INSERT INTO match_result (userid,missionid,match_time,result,award) VALUES (2,1,datetime('now'),0,null)");
        tx.executeSql("INSERT INTO match_result (userid,missionid,match_time,result,award) VALUES (3,1,datetime('now'),1,null)");
    });
    db.transaction(function (tx) {
        tx.executeSql('SELECT mission.id mid,mission.name mname,r.u_name uid FROM mission LEFT JOIN (select match_result.missionid,users.u_name, match_result.userid from match_result,users where users.id = match_result.userid and userid = 3 ) r ON mission.id = r.missionid', [], function (tx, results) {
            var len = results.rows.length;
            var i;
            for (i = 0; i < len; i++){
                $tr = $("<tr></tr>");
                $tr.appendTo($("#mytable"));
                $td1 = $("<td>" + results.rows.item(i).mid + "</td>");
                $td1.appendTo($tr);
                $td2 = $("<td>" + results.rows.item(i).mname + "</td>");
                $td2.appendTo($tr);
                $td3 = $("<td>" + results.rows.item(i).uid + "</td>");
                $td3.appendTo($tr);
            }
        }, null);
    });
</script>
</html>